-- =============================================
-- This stored procedure moves a specified item to a target location.
--
-- Notes:
-- When @InMoveLevel is "Lower", it means that the move item should go
-- right below the target item as a child. This is not the same as simply moving under
-- the target.
--
--
-- Modifications
-- 06/06/2003   E.Tatsui   Converted from NavMenuModifyCode.cfm
-- 02/06/2004   E.Tatsui   Removed transactions.
-- 09/22/2005   E.Tatsui   Fixed a problem moving an item to an empty website.
-- 09/26/2005   E.Tatsui   Fixed a problem with missing children when a parent item is moved.
-- =============================================

CREATE                            PROCEDURE amsp_CMMoveNavMenu
  @InMoveNavMenuID numeric,
  @InTargetNavMenuID numeric = NULL,
  @InMovePromoteDemoteInd char(1) = 'M',
  @InMoveLevel varchar(10) = NULL,
  @OutErrorMessage varchar(255) OUTPUT
AS
BEGIN

  DECLARE
    @WebsiteKey uniqueidentifier,
    @MinSort numeric(28,18),
    @MaxSort numeric(28,18),
    @NavContentGroupInd char(1),
    @Move_ParentNavMenuID numeric,
    @Move_AncestorNavMenuID numeric,
    @Move_CategoryDepth numeric,
    @Move_ParentName varchar(255),
    @Move_SortOrder numeric(28,18),
    @Target_CategoryDepth numeric,
    @Target_SortOrder numeric(28,18),
    @Target_AncestorNavMenuID numeric,
    @Target_ParentNavMenuID numeric,
    @LowestSortOrder numeric(28,18),
    @Below_SortOrder numeric(28,18),
    @IncrementValue numeric(28,18),
    @DescendantCount integer,
    @Counter integer,
    @Loop_NavMenuID numeric,
    @Loop_SortOrder numeric(28,18),
    @Loop_LowestSortOrder numeric(28,18),
    @New_SortOrder numeric(28,18),
    @New_ParentNavMenuID numeric,
    @New_AncestorNavMenuID numeric,
    @New_CategoryDepth numeric,
    @IsTargetDescendant bit,
    @Sib_NavMenuID numeric,
    @Sib_SortOrder numeric(28,18),
    @Level varchar(10),
    @Error integer
    
  EXEC amsp_CMNavMenuRenum
  
  -- Get info of item to move.
  SELECT @Move_ParentNavMenuID = a.ParentNavMenuID,
         @Move_AncestorNavMenuID = a.AncestorNavMenuID,
         @Move_CategoryDepth = a.CategoryDepth,
         @Move_ParentName = b.Name,
         @Move_SortOrder = a.SortOrder,
         @NavContentGroupInd = a.NavContentGroupInd,
         @WebsiteKey = a.WebsiteKey
    FROM Nav_Menu a WITH (NOLOCK) LEFT OUTER JOIN Nav_Menu b WITH (NOLOCK)
      ON a.ParentNavMenuID = b.NavMenuID
   WHERE a.NavMenuID = @InMoveNavMenuID

  IF @@Rowcount = 0
    RETURN

  -- For promote, look for the target ID
  IF @InMovePromoteDemoteInd = 'P' BEGIN
    SELECT TOP 1 @InTargetNavMenuID = NavMenuID
      FROM Nav_Menu WITH (NOLOCK)
     WHERE CategoryDepth = @Move_CategoryDepth - 1
       AND SortOrder < @Move_SortOrder
       AND NavContentGroupInd = @NavContentGroupInd
    ORDER BY SortOrder DESC

    SET @Level = 'Same'
  END
  -- Also for demote, look for the target ID
  ELSE IF @InMovePromoteDemoteInd = 'D' BEGIN
    SELECT TOP 1
           @Sib_NavMenuID = NavMenuID,
           @Sib_SortOrder = SortOrder
      FROM Nav_Menu WITH (NOLOCK)
     WHERE CategoryDepth = @Move_CategoryDepth
       AND SortOrder < @Move_SortOrder
       AND NavContentGroupInd = @NavContentGroupInd
     ORDER BY SortOrder DESC

    SELECT TOP 1
           @InTargetNavMenuID = NavMenuID
      FROM Nav_Menu WITH (NOLOCK)
     WHERE CategoryDepth = @Move_CategoryDepth + 1
       AND SortOrder < @Move_SortOrder
       AND SortOrder > @Sib_SortOrder
       AND NavContentGroupInd = @NavContentGroupInd
     ORDER BY SortOrder DESC
  
    IF @@Rowcount > 0
      SET @Level = 'Same'
    ELSE BEGIN
      SET @InTargetNavMenuID = @Sib_NavMenuID
      SET @Level = 'Lower'
    END

  END

  -- For Move, if level is specified, set to the value. Otherwise, set to "Same"
  ELSE IF @InMoveLevel IS NOT NULL
    SET @Level = @InMoveLevel
  ELSE
    SET @Level = 'Same'

  -- Make sure we found a valid target ID.
  IF @InMoveNavMenuID = @InTargetNavMenuID
    SET @OutErrorMessage = 'Error! The target item can''t be the item you want to move.'
  IF @InTargetNavMenuID IS NULL  
    SET @OutErrorMessage = 'Error! Can''t find the target item.'

  IF @OutErrorMessage IS NULL BEGIN   
    -- Build a table of item's descendants
    SELECT @MinSort = a.SortOrder,
           @MaxSort = (SELECT IsNull(Min(x.SortOrder),0)
                         FROM Nav_Menu x
                         WITH (NOLOCK)
                        WHERE x.SortOrder > a.SortOrder
                          AND x.CategoryDepth <= a.CategoryDepth)
      FROM Nav_Menu a WITH (NOLOCK)
     WHERE a.NavMenuID = @InMoveNavMenuID
    
    SELECT a.NavMenuID, a.SortOrder
      INTO #Descendants
      FROM Nav_Menu a WITH (NOLOCK)
     WHERE a.SortOrder > @MinSort
       AND a.SortOrder < @MaxSort

    SET @DescendantCount = @@Rowcount

    SELECT 1
      FROM #Descendants
     WHERE NavMenuID = @InTargetNavMenuID

    IF @@Rowcount >= 1
      SET @OutErrorMessage = 'Error! The target item can''t be a descendant of the item you want to move.'
  END -- @OutErrorMessage is null
  
  IF @OutErrorMessage IS NULL BEGIN
    -- Get info of the target item
    SELECT @Target_CategoryDepth = a.CategoryDepth,
           @Target_SortOrder = a.SortOrder,
           @Target_AncestorNavMenuID = a.AncestorNavMenuID,
           @Target_ParentNavMenuID = a.ParentNavMenuID
      FROM Nav_Menu a WITH (NOLOCK)
     WHERE NavMenuID = @InTargetNavMenuID

    IF @@Rowcount = 0
      RETURN
    SET @LowestSortOrder = @Target_SortOrder

    -- If first row (Navigation Menu/Content Groups heading) is target,
    -- we want inserted/moved items to appear up top. --->
    IF @Target_AncestorNavMenuID IS NOT NULL BEGIN
      SELECT @MinSort = a.SortOrder,
             @MaxSort = (SELECT IsNull(Min(x.SortOrder),0)
                           FROM Nav_Menu x
                           WITH (NOLOCK)
                          WHERE x.SortOrder > a.SortOrder
                            AND x.CategoryDepth <= a.CategoryDepth)
        FROM Nav_Menu a WITH (NOLOCK)
       WHERE a.NavMenuID = @InTargetNavMenuID

      SELECT a.NavMenuID, a.SortOrder
        INTO #TargetDescendants
        FROM Nav_Menu a WITH (NOLOCK)
       WHERE a.SortOrder > @MinSort
         AND a.SortOrder < @MaxSort
        ORDER BY a.SortOrder

      IF @@RowCount > 0
        SELECT @LowestSortOrder = Max(SortOrder)
          FROM #TargetDescendants
    END
    ELSE
      SET @Level = 'Lower'

    -- Also get information about the item below
    SELECT TOP 1 @Below_SortOrder = SortOrder
      FROM Nav_Menu WITH (NOLOCK)
     WHERE SortOrder > @LowestSortOrder
     ORDER BY SortOrder

    -- Create a cursor for Descendant
    DECLARE c_Descendant CURSOR LOCAL SCROLL FOR
    SELECT NavMenuID, SortOrder
      FROM #Descendants
     ORDER BY SortOrder

    SET @Counter = 1
    SET @New_SortOrder = @Move_SortOrder
    SET @New_ParentNavMenuID = @Move_ParentNavMenuID
    SET @New_AncestorNavMenuID = @Move_AncestorNavMenuID
    SET @New_CategoryDepth = @Move_CategoryDepth

    -- At the same level?
    IF @Level = 'Same' BEGIN
      -- Creating a new main item.
      IF @Target_CategoryDepth = 1 BEGIN
        -- If we're moving around main menu items, adjust sort order and nothing else.
        IF @Move_CategoryDepth = 1 BEGIN
print 'case 1'
          SET @New_SortOrder = (@LowestSortOrder + @Below_SortOrder)/2

        END -- Ends @Move_CategoryDepth = 1
        ELSE BEGIN -- Something is promoted.
print 'case 2'
          SET @New_ParentNavMenuID = NULL
          SET @New_AncestorNavMenuID = @InMoveNavMenuID
          SET @New_CategoryDepth = @Target_CategoryDepth
          SET @New_SortOrder = (@LowestSortOrder + @Below_SortOrder)/2
        END -- Ends something is promoted.
      END -- Ends @Target_CategoryDepth = 1
      ELSE BEGIN -- Not creating main menu.
print 'case 3'
        SET @New_ParentNavMenuID = @Target_ParentNavMenuID
        SET @New_AncestorNavMenuID = @Target_AncestorNavMenuID
        SET @New_CategoryDepth = @Target_CategoryDepth
        SET @New_SortOrder = (@LowestSortOrder + @Below_SortOrder)/2

      END -- Ends not creating main menu
    END -- Ends @Level = 'Same'
    ELSE IF @Level = 'Lower' BEGIN
      -- Is target Web site name?
      IF @Target_CategoryDepth = 0 BEGIN
        -- Change BelowSortOrder to sort order of second item in menu.
        IF @NavContentGroupInd = 'N'
          SELECT TOP 1  @Below_SortOrder = SortOrder
            FROM Nav_Menu
           WHERE CategoryDepth = 1
             AND NavContentGroupInd = @NavContentGroupInd
             AND WebsiteKey = @WebsiteKey
         AND NavMenuID <> @InMoveNavMenuID
           ORDER BY SortOrder
        ELSE
          SELECT TOP 1  @Below_SortOrder = SortOrder
            FROM Nav_Menu
           WHERE CategoryDepth = 1
             AND NavContentGroupInd = @NavContentGroupInd
             AND NavMenuID <> @InMoveNavMenuID
           ORDER BY SortOrder
print 'case 4'
        SET @Target_CategoryDepth = 1
        SET @New_ParentNavMenuID = NULL
        SET @New_AncestorNavMenuID = @InMoveNavMenuID
        SET @New_CategoryDepth = @Target_CategoryDepth
        SET @New_SortOrder = @Target_SortOrder + (@Below_SortOrder - @Target_SortOrder)/2
      END

      ELSE BEGIN -- Target is not Web site name.
        -- If item to move is a main menu item
        IF @Move_CategoryDepth = 1 BEGIN
print 'case 5'
          SET @New_ParentNavMenuID = @InTargetNavMenuID
          SET @New_AncestorNavMenuID = @Target_AncestorNavMenuID
          SET @New_CategoryDepth = @Target_CategoryDepth + 1
          SET @New_SortOrder = (@LowestSortOrder + @Below_SortOrder)/2
        END
        -- Otherwise, replace parent directory with target's directory.
        ELSE BEGIN
          -- If item to move is a descendant of target, or we are moving to
          -- right below the target on purpose, change l_BelowSortOrder to sort
          -- order of item immediately below target.
          SET @IsTargetDescendant = 0
    
          IF @Target_AncestorNavMenuID IS NOT NULL BEGIN
            SELECT @IsTargetDescendant = 1
              FROM #TargetDescendants
             WHERE NavMenuID = @InMoveNavMenuID
          END

          IF @IsTargetDescendant = 1 OR @InMoveLevel = 'Lower'  BEGIN
            IF @NavContentGroupInd = 'N'
              SELECT TOP 1 @Below_SortOrder = SortOrder
                FROM Nav_Menu
               WHERE SortOrder > @Target_SortOrder
                 AND WebsiteKey = @WebsiteKey
               ORDER BY SortOrder
            ELSE
              SELECT TOP 1 @Below_SortOrder = SortOrder
                FROM Nav_Menu
               WHERE SortOrder > @Target_SortOrder
                 AND NavContentGroupInd = @NavContentGroupInd
               ORDER BY SortOrder
          END -- Ends: the item to move is a descendant of target
print 'case 6'
          SET @New_ParentNavMenuID = @InTargetNavMenuID
          SET @New_AncestorNavMenuID = @Target_AncestorNavMenuID
          SET @New_CategoryDepth = @Target_CategoryDepth + 1
          SET @New_SortOrder = (@Target_SortOrder + @Below_SortOrder) /2
        END -- Ends : Not moving a main item.
      END -- Ends: Target is not Web site name.
    
    END -- Ends @Level = Lower


    -- Update the item
    UPDATE Nav_Menu
       SET ParentNavMenuID = @New_ParentNavMenuID,
           AncestorNavMenuID = @New_AncestorNavMenuID,
           CategoryDepth = @New_CategoryDepth,
           SortOrder = @New_SortOrder,
                 WorkflowStatusCode = 'W',
                     PublishedDateTime = NULL
       WHERE NavMenuID = @InMoveNavMenuID

    -- Make sure directory name is unique.
    EXECUTE amsp_CMGetUniqueDirectoryName @InMoveNavMenuID, 1, NULL


    -- Update properties of the old parent record.
    IF IsNull(@New_ParentNavMenuID,0) <> IsNull(@Move_ParentNavMenuID,0)
      EXEC amsp_CMUpdateNavProperties @Move_ParentNavMenuID

    SET @Loop_LowestSortOrder = @New_SortOrder
    SET @IncrementValue = (@Below_SortOrder - @Loop_LowestSortOrder) / (@DescendantCount + 2)

    -- Update descendants
    OPEN c_Descendant
    FETCH NEXT FROM c_Descendant
     INTO @Loop_NavMenuID,
          @Loop_SortOrder

    WHILE @@FETCH_STATUS = 0 BEGIN

      UPDATE Nav_Menu
         SET AncestorNavMenuID = @New_AncestorNavMenuID,
             CategoryDepth = CategoryDepth + @New_CategoryDepth - @Move_CategoryDepth,
             SortOrder = @Loop_LowestSortOrder + (@IncrementValue * @Counter),
                     WorkflowStatusCode = 'W',
                         PublishedDateTime = NULL
       WHERE NavMenuID = @Loop_NavMenuID

      EXECUTE amsp_CMGetUniqueDirectoryName @Loop_NavMenuID, 1, NULL

      SET @Counter = @Counter + 1
      FETCH NEXT FROM c_Descendant
       INTO @Loop_NavMenuID,
            @Loop_SortOrder
    END -- Ends while loop

    -- Figure out FilePath, DescendantCount and AncestoryList.
    EXEC amsp_CMUpdateNavProperties @InMoveNavMenuID

    -- Update properties of descendants
     FETCH FIRST FROM c_Descendant
      INTO @Loop_NavMenuID,
       @Loop_SortOrder
    WHILE @@FETCH_STATUS = 0 BEGIN
      -- Figure out FilePath, DescendantCount and AncestoryList
      EXEC amsp_CMUpdateNavProperties @Loop_NavMenuID

    FETCH NEXT FROM c_Descendant
     INTO @Loop_NavMenuID,
          @Loop_SortOrder
    END

    CLOSE c_Descendant
    DEALLOCATE c_Descendant

  END -- @OutErrorMessage is null
END

GO
GRANT EXECUTE ON  [dbo].[amsp_CMMoveNavMenu] TO [IMIS]
GO